<?php
use TbfDatabase\SetTableData\SetTableData;
use TbfDatabase\Orm\OrmObject;
use TbfDatabase\MysqlAST\SelectCommand;
/*
tbf 数据库
TODO 表名和字段名正确转义
本类不知道任何和字段相关的信息
*/
class TbfDatabase {
    /** @var \PDO */
    private static $pdo = null;

    /** @return \PDO */
    static function getPdo(){
        if (self::$pdo!=null){
            return self::$pdo;
        }
        self::$pdo = new PDO(sprintf('mysql:host=%s;port=%d;dbname=%s;charset=utf8',
                TbfConfig::$DbHost,
                TbfConfig::$DbPort,
                TbfConfig::$DbDatabase),
            TbfConfig::$DbUsername,
            TbfConfig::$DbPassword
        );
        self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        self::$pdo -> setAttribute (PDO::ATTR_EMULATE_PREPARES, false);
        return self::$pdo;
    }
    /**
     * sql查询,会返回[]map[string]string
     * 使用样例 $data = TbfDatabase::query('SELECT * FROM user WHERE username=? and role=?',array('admin123','supper'));
     * 用这个函数执行没有返回值的命令(如insert) 没有问题,但是文档化不好.
     * @param $sql
     * @param array $data
     * @return array
     * @throws Exception
     */
    static function query($sql,$data=array()){
        try{
            if (empty($data)){
                $stat = self::getPdo()->query($sql);
            }else{
                $stat = self::getPdo()->prepare($sql);
                $stat->execute($data);
            }
            return $stat->fetchAll(\PDO::FETCH_ASSOC);
        }catch(Exception $e){
            throw new Exception('[TbfDatabase.query]: '.$e->getMessage().' sql: '.$sql);
        }
    }

    // sql执行,无返回值,
    // 不要用这个函数执行select命令,会报错: General error: 2014 Cannot execute queries while other unbuffered queries are active.
    static function execute($sql,$data=array()){
        try{
            if (empty($data)){
                self::getPdo()->exec($sql);
            }else{
                $stat = self::getPdo()->prepare($sql);
                $stat->execute($data);
            }
            return;
        }catch(Exception $e){
            throw new Exception('[TbfDatabase.execute] '.$e->getMessage().' sql: '.$sql);
        }
    }

    //开始数据库事务,脚本退出时,会自动rollback.
    static function beginTransaction(){
        self::getPdo()->beginTransaction();
    }
    static function commit(){
        self::getPdo()->commit();
    }
    static function rollback(){
        self::getPdo()->rollBack();
    }
    static function lastInsertId(){
        return self::getPdo()->lastInsertId();
    }

    //单条数据插入,传入表名,和本条数据格式map[string]string
    //无返回值
    static function insert($table,$row){
        if (empty($row)){
            throw new Exception('[TbfDatabase.insert] empty($data)');
        }
        $fielddata = array_keys($row);
        $valuedata = array_values($row);
        $fieldStr = '`'.implode ('`,`', $fielddata).'`';
        $valueStr = str_repeat('?,',count($row)-1).'?';
        //sql例子 INSERT INTO AdminUser (username,password) VALUES (?,?);
        $sql = sprintf('INSERT INTO `%s` (%s) VALUES (%s)',$table,$fieldStr,$valueStr);
        self::execute($sql,$valuedata);
    }

    //单条数据插入,传入表名,和本条数据格式map[string]string
    //无返回值
    static function replace($table,$row){
        if (empty($row)){
            throw new Exception('[TbfDatabase.replace] empty($data)');
        }
        $fielddata = array_keys($row);
        $valuedata = array_values($row);
        $fieldStr = '`'.implode ('`,`', $fielddata).'`';
        $valueStr = str_repeat('?,',count($row)-1).'?';
        //sql例子 REPLACE AdminUser (username,password) VALUES (?,?);
        $sql = sprintf('REPLACE `%s` (%s) VALUES (%s)',$table,$fieldStr,$valueStr);
        self::execute($sql,$valuedata);
    }

    //更新数据,传入表名,需要更新的数据,使用数据里面的'id'字段作为查询条件
    //无返回值
    static function updateById($table,$row,$pkField='id'){
        if (empty($row[$pkField])){
            throw new Exception('[TbfDatabase.update] empty($row['.$pkField.'])');
        }
        if (count($row)<=1){
            throw new Exception('[TbfDatabase.update] count($row)<=1');
        }
        $id = $row[$pkField];
        $updateValues = array();
        $updateArr = array();
        foreach($row as $key=>$value){
            if ($key==$pkField){
                continue;
            }
            $updateArr[]='`'.$key.'`=?';
            $updateValues[] = $value;
        }
        $updateValues[] = $id;
        $updateStr = implode(',',$updateArr);
        //sql例子 UPDATE AdminUser SET username=?,password=? where id = 1;
        $sql = sprintf('UPDATE `%s` SET %s where `%s` = ?',$table,$updateStr,$pkField);
        self::execute($sql,$updateValues);
    }

    static function replaceById($table,$row,$pkField='id'){
        if (count($row)<=0){
            throw new Exception('[TbfDatabase.replaceById] count($row)<=0');
        }
        $dbRow = null;
        if (!empty($row[$pkField])){
            $dbRow = self::getOneById($table,$row[$pkField],$pkField);
        }
        if (empty($dbRow)){
            self::insert($table,$row);
        }else{
            self::updateById($table,$row,$pkField);
        }
    }

    //使用id获取一条数据
    static function getOneById($table,$id,$pkField = 'id'){
        $sql = sprintf('SELECT * FROM `%s` WHERE `%s`=? LIMIT 1',$table,$pkField);
        $data = self::query($sql,array($id));
        if (empty($data)){
            return array();
        }
        return array_pop($data);
    }
    //使用单个where查询一条数据,注意参数顺序和getOneById是相反的
    static function getOneWhere($table,$field,$value){
        $sql = sprintf('SELECT * FROM `%s` WHERE `%s`=? LIMIT 1',$table,$field);
        $data = self::query($sql,array($value));
        if (empty($data)){
            return array();
        }
        return array_pop($data);
    }
    static function deleteById($table,$id,$pkField='id'){
        $sql = sprintf('DELETE FROM `%s` WHERE `%s`=?',$table,$pkField);
        self::execute($sql,array($id));
    }
    static function getAllInTable($table){
        return self::query(sprintf('SELECT * FROM `%s`',$table));
    }
    static function setDatabaseData($data){
        $obj = new SetTableData();
        $obj->setTableData($data);
    }
    static function setDatabaseDataByYaml($yaml){
        $data = \Symfony\Component\Yaml\Yaml::parse($yaml);
        if (empty($data)){
            throw new Exception("[setDatabaseDataByYaml] yaml can not parse");
        }
        $obj = new SetTableData();
        $obj->setTableData($data);
    }
    static function copyTable($from,$to){
        $sql = sprintf('CREATE TABLE %s LIKE %s; INSERT %s SELECT * FROM %s;',$to,$from,$to,$from);
        self::execute($sql);
    }

    static function runSelectCommand(SelectCommand $select){
        return self::query($select->GetPrepareSql(),$select->GetPrepareParameter());
    }


    //把使用 TbfDatabase::query 查询到的一行数据,放入orm对象中,并且返回这对象
    static function ormFromArray(OrmObject $obj,array $row){
        return TbfSerialize::objPublicFieldLoadFromArray($obj,$row);
    }
    //把一个orm对象放入数据库存储,如果不存在会插入,如果存在会更新
    static function ormPersist(OrmObject $obj){
        $row = TbfSerialize::objPublicFieldToArray($obj);
        self::replaceById($obj->getTableName(),$row,$obj->getIdFieldName());
        if ( !empty($obj->getIdFieldName()) && empty($obj->{$obj->getIdFieldName()}) ) {
            $obj->{$obj->getIdFieldName()}=self::lastInsertId();
            $row[$obj->getIdFieldName()]=self::lastInsertId();
        }
        return $row;
    }
    //使用id查询出一个orm对象来,如果没找到会返回null
    static function ormOneById(OrmObject $obj,$id){
        if (empty($id)){
            return null;
        }
        $row = TbfDatabase::getOneWhere($obj->getTableName(),$obj->getIdFieldName(),$id);
        if (empty($row)){
            return null;
        }
        return TbfDatabase::ormFromArray($obj,$row);
    }
    //使用id查询出一个orm对象来,如果没找到会返回传入的对象来.
    static function ormOneByIdOrEmpty(OrmObject $obj,$id){
        if (empty($id)){
            return $obj;
        }
        $row = TbfDatabase::getOneWhere($obj->getTableName(),$obj->getIdFieldName(),$id);
        if (empty($row)){
            return $obj;
        }
        return TbfDatabase::ormFromArray($obj,$row);
    }
    static function ormListFrom2DArray(OrmObject $obj,array $data){
        $objList = array();
        $reflectionClass = TbfSerialize::getReflectionClassFromObject($obj);
        foreach($data as $row){
            $objList[] = TbfDatabase::ormFromArray($reflectionClass->newInstance(),$row);
        }
        return $objList;
    }
}
